# _*_ coding:utf-8 _*_
import os
import io
import json
import pymysql

coon = pymysql.connect(host='127.0.0.1', user='root',
                       passwd='fan0314@', port=3306, db='fly_info', charset='utf8')
cur = coon.cursor()  # 建立游标

# 获取所有的航班线路


def get_all_lines():
    f = open("data/all_line.json")
    all_line = f.read()
    all_line_json = json.loads(all_line)
    return all_line_json


# 根据航线获取文件路径
def get_line_file_path(line):
    searchDepartureAirport = line['searchDepartureAirport']
    searchArrivalAirport = line['searchArrivalAirport']
    return {
        "path": 'data/wbdflightlist/' + searchDepartureAirport + '-' + searchArrivalAirport + ".json",
        "airport": {
            "searchDepartureAirport": searchDepartureAirport,
            "searchArrivalAirport": searchArrivalAirport
        }
    }


# 根据文件路径获取航班数据
def get_data_by_file_path(path_info):
    path = path_info['path']
    try:
        f = open(path)
        data = f.read()
        data_json = json.loads(data)
        return {
            'data': data_json,
            'airport': path_info['airport']
        }
    except:
        return {
            'data': {
                'code': 1
            }
        }


# 根据数据获取想要的字段
def get_fly_info_by_data(data):
    code = data['data']['code']
    print(code)
    # 如果成功
    if code == 0:
        searchDepartureAirport = data['airport']['searchDepartureAirport']
        searchArrivalAirport = data['airport']['searchArrivalAirport']
        flights = data['data']['data']['flights']
        res_list = []
        for flight in flights:
            if 'binfo' in flight:
                binfo = flight['binfo']
                res_item = {
                    'searchDepartureAirport': searchDepartureAirport,
                    'searchArrivalAirport': searchArrivalAirport,
                    "airCode": binfo['airCode'],
                    "arrAirport": binfo['arrAirport'],
                    "arrAirportCode": binfo['arrAirportCode'],
                    "arrDate": binfo['arrDate'],
                    "arrTerminal": binfo['arrTerminal'],
                    "arrTime": binfo['arrTime'],
                    "cabin": binfo['cabin'],
                    "date": binfo['date'],
                    "depAirport": binfo['depAirport'],
                    "depAirportCode": binfo['depAirportCode'],
                    "depDate": binfo['depDate'],
                    "depTerminal": binfo['depTerminal'],
                    "depTime": binfo['depTime'],
                    "distance": binfo['distance'],
                    "flightTime": binfo['flightTime'],
                    "fullName": binfo['fullName'],
                    "mainCarrierFullName": binfo['mainCarrierFullName'],
                    "mealDesc": binfo['mealDesc'],
                    "name": binfo['name'],
                    "planeFullType": binfo['planeFullType'],
                    "planeType": binfo['planeType'],
                    "shortCarrier": binfo['shortCarrier'],
                    "shortName": binfo['shortName'],
                    "minPrice": flight['minPrice'],
                    "code": flight['code'],
                    "flightType": flight['flightType'],
                    "discountStr": flight['discountStr']
                }
                res_list.append(res_item)
        return res_list
    else:
        return []


def executeSql(sql, data):
    cur.execute(sql, data)
    coon.commit()


def insert(data):
    sql = 'INSERT INTO `fly_info`.`wbdflightlist`(`searchDepartureAirport`, `searchArrivalAirport`, `airCode`, `arrAirport`, `arrAirportCode`, `arrDate`, `arrTerminal`, `arrTime`, `cabin`, `date`, `depAirport`, `depAirportCode`, `depDate`, `depTerminal`, `depTime`, `distance`, `flightTime`, `fullName`, `mainCarrierFullName`, `mealDesc`, `name`, `planeFullType`, `planeType`, `shortCarrier`, `shortName`, `minPrice`, `code`, `flightType`, `discountStr`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
    executeSql(sql, data)


def set_fly_info_in_DB(flys):
    print(flys)
    for fly in flys:
        searchDepartureAirport = fly['searchDepartureAirport']
        searchArrivalAirport = fly['searchArrivalAirport']
        airCode = fly['airCode']
        arrAirport = fly['arrAirport']
        arrAirportCode = fly['arrAirportCode']
        arrDate = fly['arrDate']
        arrTerminal = fly['arrTerminal']
        arrTime = fly['arrTime']
        cabin = fly['cabin']
        date = fly['date']
        depAirport = fly['depAirport']
        depAirportCode = fly['depAirportCode']
        depDate = fly['depDate']
        depTerminal = fly['depTerminal']
        depTime = fly['depTime']
        distance = fly['distance']
        flightTime = fly['flightTime']
        fullName = fly['fullName']
        mainCarrierFullName = fly['mainCarrierFullName']
        mealDesc = fly['mealDesc']
        name = fly['name']
        planeFullType = fly['planeFullType']
        planeType = fly['planeType']
        shortCarrier = fly['shortCarrier']
        shortName = fly['shortName']
        minPrice = fly['minPrice']
        code = fly['code']
        flightType = fly['flightType']
        discountStr = fly['discountStr']
        data = (searchDepartureAirport, searchArrivalAirport, airCode, arrAirport, arrAirportCode, arrDate, arrTerminal, arrTime, cabin, date, depAirport, depAirportCode, depDate, depTerminal, depTime, distance,
                flightTime, fullName, mainCarrierFullName, mealDesc, name, planeFullType, planeType, shortCarrier, shortName, minPrice, code, flightType, discountStr)
        insert(data)


if __name__ == "__main__":
    all_line = get_all_lines()
    for line in all_line:
        line_path = get_line_file_path(line)
        data = get_data_by_file_path(line_path)
        flys = get_fly_info_by_data(data)
        set_fly_info_in_DB(flys)
    # print(all_line)
